﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Pub.DBUtility
{
    /// <summary>
    /// SQLServer操作类
    /// </summary>
    internal class clsSQLServerHelper : IDataBaseService
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string strConnect = "Data Source=.;Initial Catalog=AAA;Integrated Security=True ";


        public void CreateParameters(int num, out IDataParameter[] param)
        {
            param = null; param = new SqlParameter[num];
            for (int i = 0; i < num; i++)
            {
                param[i] = new SqlParameter();
            }
        }



        /// <summary>
        /// SQL 转换
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        private String strGetSQLServerPara(String strSQL)
        {
            int intPara = 1;
            for (int idx = strSQL.IndexOf("?"); idx > 0; idx = strSQL.IndexOf("?"))
            {
                strSQL = strSQL.Substring(0, idx) + "@" + (intPara++) + strSQL.Substring(idx + 1, strSQL.Length - idx - 1);
            }
            return strSQL;
        }

        /// <summary>
        /// 获取有参数
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="dtResult"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public long GetTableWithParams(string strSQL, ref DataTable dtResult, params IDataParameter[] Params)
        {
            long lngRes = 1;
            strSQL = strGetSQLServerPara(strSQL);
            SqlCommand cmd = null;
            SqlConnection conn = new SqlConnection(strConnect);
            try
            {
                conn.Open(); cmd = new SqlCommand(strSQL, conn);
                for (int i = 0; i < Params.Length; i++)
                {
                    ((SqlParameter)Params[i]).ParameterName = (i + 1).ToString();
                    cmd.Parameters.Add((SqlParameter)Params[i]);
                }
                cmd.CommandTimeout = 3000;
                SqlDataAdapter objAdapter = new SqlDataAdapter();
                objAdapter.SelectCommand = cmd;
                dtResult = new DataTable();
                objAdapter.Fill(dtResult);
            }
            catch (System.Exception objEx)
            { lngRes = 0; throw (objEx); }
            finally
            {
                if (conn != null)
                {
                    conn.Close(); if (cmd != null)
                        cmd.Dispose(); conn.Dispose();
                }
            }
            return lngRes;
        }
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="AffectedCount"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public long ExecParamSQL(string strSQL, ref long AffectedCount, params IDataParameter[] Params)
        {
            long lngRes = 1;
            strSQL = strGetSQLServerPara(strSQL);
            SqlCommand cmd = null;
            SqlConnection conn = new SqlConnection(strConnect);
            StringBuilder sb = new StringBuilder();
            try
            {
                conn.Open();
                cmd = new SqlCommand(strSQL, conn);
                for (int i = 0; i < Params.Length; i++)
                {
                    ((SqlParameter)Params[i]).ParameterName = (i + 1).ToString();
                    cmd.Parameters.Add((SqlParameter)Params[i]);
                }
                cmd.CommandTimeout = 3000; AffectedCount = cmd.ExecuteNonQuery();
            }
            catch (System.Exception objEx) { lngRes = 0; throw (objEx); }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    if (cmd != null) cmd.Dispose(); conn.Dispose();
                }
            }
            return lngRes;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="strProcName"></param>
        /// <param name="arrParams"></param>
        /// <returns></returns>
        public long ExecProc(string strProcName, IDataParameter[] arrParams)
        {
            long lngRes = 1; SqlCommand cmd = null;
            string strTemp = strProcName; if (arrParams.Length == 0)
            {
                strTemp = "{ CALL " + strTemp + " }";
            }
            else {
                strTemp = "{ CALL " + strTemp + "(? "; for (int i = 0; i < arrParams.Length - 1; i++)
                { strTemp = strTemp + ",? "; }
                strTemp = strTemp + " )}";
            }
            SqlConnection objCnn = new SqlConnection(strConnect);
            try
            {
                objCnn.Open(); cmd = new SqlCommand(strProcName, objCnn);
                cmd.CommandType = CommandType.StoredProcedure; if ((arrParams != null) && (arrParams.Length > 0))
                {
                    for (int i = 0; i < arrParams.Length; i++)
                    {
                        SqlParameter objParam = arrParams[i] as SqlParameter;
                        if (objParam == null) continue; cmd.Parameters.Add(arrParams[i]);
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception objEx)
            {
                throw objEx;
            }
            finally
            {
                if (objCnn != null)
                {
                    objCnn.Close(); if (cmd != null) cmd.Dispose(); objCnn.Dispose();
                }
            }
            return lngRes;
        }
        /// <summary>
        /// 未实现
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public long ExecParamProc(string strSQL, params IDataParameter[] Params)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="strProcName"></param>
        /// <param name="dtResult"></param>
        /// <returns></returns>
        public long GetTableFromProc(string strProcName, ref DataTable dtResult)
        {
            SqlCommand cmd = null;
            SqlConnection objCnn = new SqlConnection(strConnect); try
            {
                cmd = new SqlCommand(strProcName, objCnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 3000;
                SqlDataAdapter objAdapter = new SqlDataAdapter();
                objAdapter.SelectCommand = cmd; dtResult = new DataTable(); objCnn.Open();
                objAdapter.Fill(dtResult);
            }
            catch (System.Exception objEx)
            {
                throw (objEx);
            }
            finally
            {
                if (objCnn != null)
                {
                    objCnn.Close();
                    if (cmd != null) cmd.Dispose(); objCnn.Dispose();
                }
            }
            return 1;
        }

        public void m_mthCreateDB(string DBName)
        {
            throw new NotImplementedException();
        }

        public void m_mthChagePassword()
        {
            throw new NotImplementedException();
        }

        public int ExecuteSqlTran(List<string> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(strConnect))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

    }
}
